/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package person.notification;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.FacesConverter;
import system.general.Exc;
import system.general.Logged;
import system.general.Persistencia;

/**
 *
 * @author gilberto
 */
@ManagedBean(name = "person_notificationFind")
@FacesConverter(value = "person_notificationFind")
@RequestScoped
public class Find implements Converter {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Beans selectedBean = new Beans();
    private String sql = "";
    private String command = "SELECT sm.id, sm.id_emisor, pg.nombre, pg.apellido, "
            + "CASE WHEN pd.archivo != '' THEN pd.archivo ELSE 'perfil0.jpg' END AS archivo, sm.mensaje, sm.fecha, pm.estatus "
            + "FROM public.v_sistema_mensaje sm "
            + "LEFT JOIN public.v_persona_mensaje pm "
            + "ON (sm.id = pm.id_mensaje) "
            + "INNER JOIN public.v_persona_general pg "
            + "ON (sm.id_emisor = pg.id) "
            + "LEFT JOIN public.v_persona_documento pd "
            + "ON (pg.id = pd.id_persona AND pd.idl_tipo = 6) WHERE ";
    private String order = "ORDER BY sm.fecha DESC, pm.estatus ";
    public List<person.autocomplete.Beans> SelectedBeans;
    private person.autocomplete.Find person_autocompleteFind = new person.autocomplete.Find();
    private List<Integer> ids = new ArrayList<>();

    public Find() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
    }

    public Beans getSelectedBean() {
        return selectedBean;
    }

    public void setSelectedBean(Beans selectedBeans) {
        this.selectedBean = selectedBeans;
    }

    public List<Beans> getListByLoggedMap() throws Exc, SQLException, Exception {
        if (logged.getMap().isEmpty()) {
            return null;
        }
        return byMap(logged.getMap());
    }

    public List<Beans> getListByLoggedId() throws Exc, SQLException, Exception {
        if (logged.getLoggedId() != 0) {
            Map<String, String> map = new LinkedHashMap<>();
            map.put("sm.id = " + logged.getDetailId(), " ");
            return byMap(map);
        }
        return null;
    }

    public boolean byExist(Map map) throws Exc, SQLException, Exception {
        List<Beans> listBean = byMap(map);
        if (listBean.isEmpty()) {
            listBean.clear();
            return false;
        }
        listBean.clear();
        return true;
    }

    public List<Beans> byMap(Map map) throws Exc, SQLException, Exception {
        Iterator it = map.entrySet().iterator();
        StringBuilder criteria = new StringBuilder("");
        while (it.hasNext()) {
            Map.Entry e = (Map.Entry) it.next();
            criteria.append(e.getKey().toString());
            criteria.append(e.getValue().toString());
        }
        sql = command + criteria + order;
        return executeSql(sql);
    }

    public List<person.autocomplete.Beans> autoComplete(String data) throws Exc, SQLException, Exception {
        data = data.replace(" ", "|");
        Map<String, String> map = new LinkedHashMap<>();
        map.put("lower(pg.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(pg.apellido) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(pc.detalle) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(pu.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " ");
        return person_autocompleteFind.byMap(map);
    }

    public List<Beans> executeSql(String sql) throws Exc, SQLException, Exception {
        List<Beans> listBean = new ArrayList<>();
        ResultSet rsts = persistencia.executeRsts(sql);
        while (rsts.next()) {
            Beans beans = new Beans(
                    rsts.getString("nombre"),
                    rsts.getString("apellido"),
                    rsts.getInt("id"),
                    rsts.getInt("id_emisor"),
                    rsts.getString("mensaje"),
                    rsts.getTimestamp("fecha"),
                    rsts.getBoolean("estatus"),
                    rsts.getString("archivo"));
            listBean.add(beans);
        }
        //Desconecta de la base de datos debido a que es un resultset y podria quedar la conexion activa
        persistencia.dbDisconnect();
        return listBean;
    }

    public String strChar(String data) {
        data = data.toLowerCase().trim();
        data = data.replaceAll("á", "a");
        data = data.replaceAll("é", "e");
        data = data.replaceAll("í", "i");
        data = data.replaceAll("ó", "o");
        data = data.replaceAll("ú", "u");
        data = data.replaceAll("a", "[a,á]");
        data = data.replaceAll("e", "[e,é]");
        data = data.replaceAll("i", "[i,í]");
        data = data.replaceAll("o", "[o,ó]");
        data = data.replaceAll("u", "[u,ú]");
        return data;
    }

    @Override
    public Object getAsObject(FacesContext facesContext, UIComponent component, String submittedValue) {
        if (submittedValue.trim().equals("")) {
            return null;
        }
        ids.add(Integer.parseInt(submittedValue.substring(0, submittedValue.indexOf("-"))));
        logged.setList_receptor(ids);
        return null;
    }

    @Override
    public String getAsString(FacesContext facesContext, UIComponent component, Object value) {
        if (((person.autocomplete.Beans) value).getGeneralBeans().getId() == 0) {
            return "";

        }

        return String.valueOf(((person.autocomplete.Beans) value).getGeneralBeans().getId()) + "-" + ((person.autocomplete.Beans) value).getGeneralBeans().getNombre() + " " + ((person.autocomplete.Beans) value).getGeneralBeans().getApellido();
    }

    public List<person.autocomplete.Beans> getSelectedBeans() {
        return SelectedBeans;
    }

    public void setSelectedBeans(List<person.autocomplete.Beans> SelectedBeans) {
        this.SelectedBeans = SelectedBeans;
    }
}
